package test2;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class Student {
	private String stuno;
	private String name;
	private int age;
	private Date birthdate;

	public Student(String stuno, String name, int age, Date birthdate) {
		super();
		this.stuno = stuno;
		this.name = name;
		this.age = age;
		this.birthdate = birthdate;
	}

	public String getStuno() {
		return stuno;
	}

	public void setStuno(String stuno) {
		this.stuno = stuno;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Date getBirthdate() {
		return birthdate;
	}

	public void setBirthdate(Date birthdate) {
		this.birthdate = birthdate;
	}

	@Override
	public String toString() {
		return "Student [stuno=" + stuno + ", name=" + name + ", age=" + age + ", birthdate=" + birthdate + "]"+"\n";
	}

}

public class Mainboke1402 {

	static String driverName = "com.mysql.jdbc.Driver";
	static String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=true";
	static String userName = "root";
	static String password = "kimheechul";

	public static void main(String[] args) {
		displayAll();
		// insert(new Student("2015001", "kimheechul",
		// 34,Date.valueOf("1983-07-10")));
		// deleteStudent(19);
		//displayAllOrderByIdDesc(-1);
		updateStudentAge();
		displayAll();
	}

	public static void displayAll() {//201521123006
		Connection con = null;
		Statement statement = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			String strSql = "select stuno,name,age,birthdate from student";
			System.out.println("学号\t\t姓名\t\t年龄\t\t出生日期");
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(strSql);
			while (rs.next()) {
				System.out.print(rs.getString("stuno") + "\t\t");// 根据列名取值
				System.out.print(rs.getString(2) + "\t\t");// 根据列的顺序取值，如name在上述Sql语句中的第2列
				System.out.print(rs.getInt("age") + "\t\t");// 根据列名取值
				System.out.print(rs.getDate(4) + "\t\n");// 根据列的顺序取值，如major在第4列
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			System.out.println("释放所有资源....");
			try {
				if (con != null)
					con.close();// 关闭连接
				con = null; // 促使GC尽快回收资源
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void insert(Student stu) {//201521123006
		int resultNum = 0;
		Connection con = null;
		Statement statement = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			String strSql = "insert into student (stuno,name,age,birthdate)" + "values('" + stu.getStuno() + "','"
					+ stu.getName() + "','" + stu.getAge() + "','" + stu.getBirthdate() + "');";
			System.out.println(strSql);
			statement = con.createStatement();
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum + "条记录已经插入");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			System.out.println("释放所有资源....");
			try {
				if (con != null)
					con.close();// 关闭连接
				con = null; // 促使GC尽快回收资源
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void displayAllOrderByIdDesc(int id) {//201521123006
		Connection con = null;
		Statement statement = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			String strSql = "select * from student order by id desc";
			System.out.println("ID\t\t姓名\t\t出生日期");
			statement = con.createStatement();
			ResultSet rs = statement.executeQuery(strSql);
			while (rs.next()) {
				System.out.print(rs.getInt("id") + "\t\t");// 根据列名取值
				System.out.print(rs.getString("name") + "\t\t");
				System.out.print(rs.getDate(5) + "\t\n");// 根据列的顺序取值，如major在第4列
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			System.out.println("释放所有资源....");
			try {
				if (con != null)
					con.close();// 关闭连接
				con = null; // 促使GC尽快回收资源
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void deleteStudent(int id) {//201521123006
		Connection con = null;
		Statement statement = null;
		int resultNum = 0;
		try {
			con = DriverManager.getConnection(url, userName, password);
			String strSql = "delete from student where id=" + id + ";";// 删除id=2的数据
			System.out.println(strSql);
			statement = con.createStatement();
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum + "条记录删除完毕");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			System.out.println("释放所有资源....");
			try {

				if (con != null)
					con.close();// 关闭连接
				con = null; // 促使GC尽快回收资源
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	public static void updateStudentAge() {//201521123006
		int resultNum = 0;
		Connection con = null;
		Statement statement = null;
		try {
			con = DriverManager.getConnection(url, userName, password);
			String strSql = "update student set age=age+1;";
			statement = con.createStatement();
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum + "条记录更新完毕");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			System.out.println("释放所有资源....");
			try {

				if (con != null)
					con.close();// 关闭连接
				con = null; // 促使GC尽快回收资源
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
}
